﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public sealed class D_FIM_Cheques : IDAL_FIM_Cheques
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        //

        public D_FIM_Cheques()
        {

        }

        public IList<M_FIM_Cheques> GetFull(Nullable<int> bank_acc_seq, Nullable<int> cheque_book_seq
            , string cheque_status)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Cheques> infoList = new List<M_FIM_Cheques>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bank_acc_seq", bank_acc_seq);
                dbManager.AddParameters(2, "@cheque_book_seq", cheque_book_seq);
                dbManager.AddParameters(3, "@cheque_status", cheque_status);

                strSQL = "SELECT che.cheque_seq, che.cheque_book_seq, che.cheque_no, che.bank_acc_seq"
                    + ", che.cheque_date, che.pay_date, che.receiver, che.remarks, che.cheque_status, che.actual_pay_date"

                    + ", cb.cheque_book_no"
                    + ", CONCAT(che.cheque_no, ' : ', acc.bank_acc_name) AS cheque_name"
                    + ", acc.bank_acc_no, acc.bank_acc_name, pc.amount"
                    + ", case che.cheque_status when '1' then 'พร้อมใช้'"
                        + " when '2' then 'จ่ายแล้ว'"
                        + " when '3' then 'ขึ้นเงินแล้ว'"
                        + " else 'ยกเลิก'"
                        + " end as cheque_status_name"

                    + ", che.record_status, rs.record_status_name"
                    + ", che.created_by, che.created_when, che.updated_by, che.updated_when"
                    + " FROM fim_cheques che"
                    + " LEFT JOIN fim_cheque_books cb ON che.cheque_book_seq = cb.cheque_book_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON che.bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN (select pc.cheque_seq"
                                + ", sum(pc.amount) as amount"
                                + " from fim_payment_cheques pc"
                                + " where pc.record_status not like @delete_status"
                                + " group by pc.cheque_seq"
                                + ") pc ON che.cheque_seq = pc.cheque_seq"
                    //+ " LEFT JOIN fim_mas_bank_branchs bb ON acc.branch_seq = bb.branch_seq"
                    //+ " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN sys_record_status rs ON che.record_status = rs.record_status"
                    + " WHERE che.record_status not like @delete_status"
                    + (bank_acc_seq != null ? " and che.bank_acc_seq = @bank_acc_seq" : "")
                    + (cheque_book_seq != null ? " and che.cheque_book_seq = @cheque_book_seq" : "")
                    + (cheque_status != null ? " and che.cheque_status = @cheque_status" : "");
                //

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Cheques info = new M_FIM_Cheques();
                        info.cheque_seq = Convert.ToInt32(drc[i]["cheque_seq"]);
                        info.cheque_book_seq = cn.ConvertNullInt(drc[i]["cheque_book_seq"]);
                        info.cheque_no = cn.ConvertNullInt(drc[i]["cheque_no"]);
                        info.cheque_name = drc[i]["cheque_name"].ToString();
                        info.bank_acc_seq = cn.ConvertNullInt(drc[i]["bank_acc_seq"]);
                        info.cheque_date = cn.ConvertNullDateTime(drc[i]["cheque_date"]);
                        info.pay_date = cn.ConvertNullDateTime(drc[i]["pay_date"]);
                        info.receiver = drc[i]["receiver"].ToString();
                        info.remarks = drc[i]["remarks"].ToString();
                        info.cheque_status = drc[i]["cheque_status"].ToString();
                        info.cheque_book_no = drc[i]["cheque_book_no"].ToString();
                        info.bank_acc_no = drc[i]["bank_acc_no"].ToString();
                        info.bank_acc_name = drc[i]["bank_acc_name"].ToString();
                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.cheque_status_name = drc[i]["cheque_status_name"].ToString();
                        info.actual_pay_date = cn.ConvertNullDateTime(drc[i]["actual_pay_date"]);

                        info.record_status = drc[i]["record_status"].ToString();
                        info.record_status_name = drc[i]["record_status_name"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_Cheques> GetList(Nullable<int> bank_acc_seq, Nullable<int> pay_seq
            , Nullable<int> cheque_book_seq, string cheque_status)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Cheques> infoList = new List<M_FIM_Cheques>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bank_acc_seq", bank_acc_seq);
                dbManager.AddParameters(2, "@pay_seq", pay_seq);
                dbManager.AddParameters(3, "@cheque_book_seq", cheque_book_seq);
                dbManager.AddParameters(4, "@cheque_status", cheque_status);

                strSQL = "SELECT che.cheque_seq, che.cheque_no"
                    + ", CONCAT(CAST(cb.cheque_book_no as varchar), ' / ', CAST(che.cheque_no as varchar)) AS cheque_name"
                    + " FROM fim_cheques che"
                    + " LEFT JOIN fim_cheque_books cb ON che.cheque_book_seq = cb.cheque_book_seq"
                    //+ " LEFT JOIN fim_payment_cheques pc ON che.cheque_seq = pc.cheque_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON che.bank_acc_seq = acc.bank_acc_seq"
                    + " WHERE che.record_status not like @delete_status"
                    + (bank_acc_seq != null ? " and che.bank_acc_seq = @bank_acc_seq" : "")
                    //+ (pay_seq != null ? " AND (pc.pay_seq is null OR pc.pay_seq = @pay_seq)"
                    //    : " AND (pc.pay_seq is null)")
                    + (cheque_book_seq != null ? " and che.cheque_book_seq = @cheque_book_seq" : "")
                    + (cheque_status != null ? " and che.cheque_status = @cheque_status" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Cheques info = new M_FIM_Cheques();
                        info.cheque_seq = Convert.ToInt32(drc[i]["cheque_seq"]);
                        info.cheque_no = cn.ConvertNullInt(drc[i]["cheque_no"]);
                        info.cheque_name = drc[i]["cheque_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_FIM_Cheques GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_Cheques info = new M_FIM_Cheques();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT che.cheque_seq, che.cheque_book_seq, che.cheque_no, che.bank_acc_seq"
                    + ", che.cheque_date, che.pay_date, che.receiver, che.remarks, che.cheque_status, che.actual_pay_date"

                    + ", cb.cheque_book_no"
                    + ", CONCAT(che.cheque_no, ' : ', acc.bank_acc_name) AS cheque_name"
                    + ", acc.bank_acc_no, acc.bank_acc_name, pc.amount"
                    + ", case che.cheque_status when '1' then 'พร้อมใช้'"
                        + " when '2' then 'จ่ายแล้ว'"
                        + " when '3' then 'ขึ้นเงินแล้ว'"
                        + " else 'ยกเลิก'"
                        + " end as cheque_status_name"

                    + ", che.record_status, rs.record_status_name"
                    + ", che.created_by, che.created_when, che.updated_by, che.updated_when"
                    + " FROM fim_cheques che"
                    + " LEFT JOIN fim_cheque_books cb ON che.cheque_book_seq = cb.cheque_book_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON che.bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN (select pc.cheque_seq"
                                + ", sum(pc.amount) as amount"
                                + " from fim_payment_cheques pc"
                                + " where pc.record_status not like 'D'"
                                + " group by pc.cheque_seq"
                                + ") pc ON che.cheque_seq = pc.cheque_seq"
                    //+ " LEFT JOIN fim_mas_bank_branchs bb ON acc.branch_seq = bb.branch_seq"
                    //+ " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN sys_record_status rs ON che.record_status = rs.record_status"
                    + " WHERE che.cheque_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.cheque_seq = Convert.ToInt32(drc[0]["cheque_seq"]);
                    info.cheque_book_seq = cn.ConvertNullInt(drc[0]["cheque_book_seq"]);
                    info.cheque_no = cn.ConvertNullInt(drc[0]["cheque_no"]);
                    info.cheque_name = drc[0]["cheque_name"].ToString();
                    info.bank_acc_seq = Convert.ToInt32(drc[0]["bank_acc_seq"]);
                    info.cheque_date = cn.ConvertNullDateTime(drc[0]["cheque_date"]);
                    info.pay_date = cn.ConvertNullDateTime(drc[0]["pay_date"]);
                    info.receiver = drc[0]["receiver"].ToString();
                    info.remarks = drc[0]["remarks"].ToString();
                    info.cheque_status = drc[0]["cheque_status"].ToString();
                    info.cheque_book_no = drc[0]["cheque_book_no"].ToString();
                    info.bank_acc_no = drc[0]["bank_acc_no"].ToString();
                    info.bank_acc_name = drc[0]["bank_acc_name"].ToString();
                    info.amount = cn.ConvertNullDouble(drc[0]["amount"]);
                    info.cheque_status_name = drc[0]["cheque_status_name"].ToString();
                    info.actual_pay_date = cn.ConvertNullDateTime(drc[0]["actual_pay_date"]);

                    info.record_status = drc[0]["record_status"].ToString();
                    info.record_status_name = drc[0]["record_status_name"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_FIM_Cheques GetInfo(string cheque_no)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_Cheques info = new M_FIM_Cheques();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@cheque_no", cheque_no);

                strSQL = "SELECT che.cheque_seq, che.cheque_book_seq, che.cheque_no, che.bank_acc_seq"
                    + ", che.cheque_date, che.pay_date, che.receiver, che.remarks, che.cheque_status, che.actual_pay_date"

                    + ", cb.cheque_book_no"
                    + ", CONCAT(che.cheque_no, ' : ', acc.bank_acc_name) AS cheque_name"
                    + ", acc.bank_acc_no, acc.bank_acc_name, pc.amount"
                    + ", case che.cheque_status when '1' then 'พร้อมใช้'"
                        + " when '2' then 'จ่ายแล้ว'"
                        + " when '3' then 'ขึ้นเงินแล้ว'"
                        + " else 'ยกเลิก'"
                        + " end as cheque_status_name"

                    + ", che.record_status, rs.record_status_name"
                    + ", che.created_by, che.created_when, che.updated_by, che.updated_when"
                    + " FROM fim_cheques che"
                    + " LEFT JOIN fim_cheque_books cb ON che.cheque_book_seq = cb.cheque_book_seq"
                    + " LEFT JOIN fim_mas_bank_accounts acc ON che.bank_acc_seq = acc.bank_acc_seq"
                    + " LEFT JOIN (select pc.cheque_seq"
                                + ", sum(pc.amount) as amount"
                                + " from fim_payment_cheques pc"
                                + " where pc.record_status not like 'D'"
                                + " group by pc.cheque_seq"
                                + ") pc ON che.cheque_seq = pc.cheque_seq"
                    //+ " LEFT JOIN fim_mas_bank_branchs bb ON acc.branch_seq = bb.branch_seq"
                    //+ " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN sys_record_status rs ON che.record_status = rs.record_status"
                    + " WHERE che.cheque_no = @cheque_no"
                    + " and che.record_status not like 'D'";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.cheque_seq = Convert.ToInt32(drc[0]["cheque_seq"]);
                    info.cheque_book_seq = cn.ConvertNullInt(drc[0]["cheque_book_seq"]);
                    info.cheque_no = cn.ConvertNullInt(drc[0]["cheque_no"]);
                    info.cheque_name = drc[0]["cheque_name"].ToString();
                    info.bank_acc_seq = Convert.ToInt32(drc[0]["bank_acc_seq"]);
                    info.cheque_date = cn.ConvertNullDateTime(drc[0]["cheque_date"]);
                    info.pay_date = cn.ConvertNullDateTime(drc[0]["pay_date"]);
                    info.receiver = drc[0]["receiver"].ToString();
                    info.remarks = drc[0]["remarks"].ToString();
                    info.cheque_status = drc[0]["cheque_status"].ToString();
                    info.cheque_book_no = drc[0]["cheque_book_no"].ToString();
                    info.bank_acc_no = drc[0]["bank_acc_no"].ToString();
                    info.bank_acc_name = drc[0]["bank_acc_name"].ToString();
                    info.amount = cn.ConvertNullDouble(drc[0]["amount"]);
                    info.cheque_status_name = drc[0]["cheque_status_name"].ToString();
                    info.actual_pay_date = cn.ConvertNullDateTime(drc[0]["actual_pay_date"]);

                    info.record_status = drc[0]["record_status"].ToString();
                    info.record_status_name = drc[0]["record_status_name"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public string Insert(M_FIM_Cheques info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(13);

                dbManager.AddParameters(0, "@cheque_seq", info.cheque_seq);
                dbManager.AddParameters(1, "@cheque_book_seq", info.cheque_book_seq);
                dbManager.AddParameters(2, "@cheque_no", info.cheque_no);
                dbManager.AddParameters(3, "@bank_acc_seq", info.bank_acc_seq);
                dbManager.AddParameters(4, "@pay_date", info.pay_date);
                dbManager.AddParameters(5, "@receiver", info.receiver);
                dbManager.AddParameters(6, "@remarks", info.remarks);
                dbManager.AddParameters(7, "@cheque_status", info.cheque_status);
                dbManager.AddParameters(8, "@actual_pay_date", info.actual_pay_date);

                dbManager.AddParameters(9, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(10, "@created_by", info.created_by);
                dbManager.AddParameters(11, "@created_when", info.created_when);
                dbManager.AddParameters(12, "@cheque_date", info.cheque_date);

                strSQL = "INSERT INTO fim_cheques"
                    + "(cheque_book_seq, cheque_no, bank_acc_seq, cheque_date, pay_date, receiver, remarks, cheque_status, actual_pay_date"
                    + ", record_status, created_by, created_when)"
                    + " VALUES (@cheque_book_seq, @cheque_no, @bank_acc_seq, @cheque_date, @pay_date, @receiver, @remarks, @cheque_status, @actual_pay_date"
                        + ", @record_status, @created_by, @created_when);"
                    + " select scope_identity();";

                var result = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();

                return result;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_FIM_Cheques info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(12);

                dbManager.AddParameters(0, "@cheque_seq", info.cheque_seq);
                dbManager.AddParameters(1, "@cheque_book_seq", info.cheque_book_seq);
                dbManager.AddParameters(2, "@cheque_no", info.cheque_no);
                dbManager.AddParameters(3, "@bank_acc_seq", info.bank_acc_seq);
                dbManager.AddParameters(4, "@pay_date", info.pay_date);
                dbManager.AddParameters(5, "@receiver", info.receiver);
                dbManager.AddParameters(6, "@remarks", info.remarks);
                dbManager.AddParameters(7, "@cheque_status", info.cheque_status);
                dbManager.AddParameters(8, "@actual_pay_date", info.actual_pay_date);

                dbManager.AddParameters(9, "@updated_by", info.updated_by);
                dbManager.AddParameters(10, "@updated_when", info.updated_when);
                dbManager.AddParameters(11, "@cheque_date", info.cheque_date);

                if (info.cheque_status == "3" && info.actual_pay_date != null)
                {
                    strSQL = "select pay_seq from fim_payment_cheques where cheque_seq = @cheque_seq";
                    DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                    foreach (DataRow dr in dtb.Rows)
                    {
                        strSQL = "UPDATE fim_payments"
                        + " set real_pay_date = @actual_pay_date"

                        + ", updated_by = @updated_by"
                        + ", updated_when = @updated_when"
                        + " WHERE pay_seq = " + dr[0].ToString();
                        dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                    }
                }

                strSQL = "UPDATE fim_cheques"
                    + " set cheque_no = @cheque_no"
                    + ", bank_acc_seq = @bank_acc_seq"
                    + ", cheque_date = @cheque_date"
                    + ", pay_date = @pay_date"
                    + ", receiver = @receiver"
                    + ", remarks = @remarks"
                    + ", cheque_status = @cheque_status"
                    + ", actual_pay_date = @actual_pay_date"

                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE cheque_seq = @cheque_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            postFimTranfers(info.cheque_seq, info.updated_by);
        }

        protected void postFimTranfers(int ChequeSeq, int? empSeq)
        {
            var paycheque = new D_FIM_PaymentCheques();
            IList<M_FIM_PaymentCheques> paycheq = paycheque.GetFull(null, ChequeSeq);
             
            //ใส่ index เป็น 0 เพราะมีรายการเช็คเพียงรายการเดียวตาม Bizz. Rule
            if (paycheq.Count == 0) {  return; }

            int? bgmSourceSeq, bgmYear;
            GetSourceAndBgmYearFromCheckSeq(ChequeSeq, out bgmSourceSeq, out bgmYear);
            M_FIM_Cheques cheq = this.GetInfo(paycheq[0].cheque_seq.ToString());

            string strChequeBookSeq = cheq.cheque_book_seq.ToString();
            string strChequeSeq = cheq.cheque_seq.ToString();
            string strBankAccSeq = cheq.bank_acc_seq.ToString();
            string strChequeNo = cheq.cheque_no.ToString();
            string strPaydate = cheq.pay_date.ToString();
            string strChequedate = cheq.cheque_date.ToString();
            string strReceiver = cheq.receiver;
            string strRemarks = cheq.remarks;
            string strChequeStatus = cheq.cheque_status;
            string strUserID = empSeq.ToString();
            Nullable<DateTime> dtActualPayDate = cheq.actual_pay_date;
            DateTime dtNow = DateTime.Now;
            var transfer = new D_FIM_Transfers();
            var acc = new D_FIM_MAS_BankAccounts();

            if (strChequeStatus == "3" && dtActualPayDate == null)
            {
                //ไม่สามารถแก้ไขข้อมูลได้ เนื่องจากลงบัญชีแล้ว จึงรีเทิร์นกลับ                
                return;
            }
            else if ((strChequeStatus == "2" || strChequeStatus == "3") && cheq.pay_date == null)
            {
                //ไม่สามารถแก้ไขข้อมูลได้ เนื่องจากลงบัญชีแล้ว จึงรีเทิร์นกลับ
                return;
            }

            if (strChequeStatus == "3")
            {
                var modelCheque = this.GetInfo(strChequeSeq);
                var modelBankAccount = acc.GetInfo(modelCheque.bank_acc_seq ?? 0);

                if (modelBankAccount.saving_bank_acc_seq != null)
                {
                    var models = transfer.GetFullFromCheque(ChequeSeq);
                    double SumAmount = cn.ConvertNullDouble(paycheque.SumMoney(strChequeSeq)) ?? 0;

                    if (models.Count > 0)
                    {
                        if (models[0].tran_grp_seq == null)
                        {
                            models[0].transfer_date = dtActualPayDate;
                            models[0].updated_by = cn.ConvertNullInt(strUserID);
                            models[0].updated_when = dtNow;

                            transfer.Update(models[0]);
                        }
                        else
                        {
                            //ไม่สามารถแก้ไขข้อมูลได้ เนื่องจากลงบัญชีแล้ว จึงรีเทิร์นกลับ
                            return;
                        }
                    }
                    else
                    {
                        var model = new M_FIM_Transfers();

                        model.bgm_source_seq = cn.ConvertNullInt(bgmSourceSeq) ?? 0;
                        model.work_area_seq = 0;
                        model.bgm_year = cn.ConvertNullInt(bgmYear) ?? 0;
                        model.transfer_type = "3";
                        model.transfer_name = "โอนอัตโนมัติจากการจ่ายเช็ค " + strChequeNo;
                        model.ref_no = "auto transection";
                        model.ref_no_outside = null;
                        model.transfer_date = dtActualPayDate;
                        model.from_bank_acc_seq = modelCheque.bank_acc_seq;
                        model.to_bank_acc_seq = modelBankAccount.saving_bank_acc_seq;
                        model.bank_acc_type_seq = 1;
                        model.amount = SumAmount;
                        model.emp_seq = cn.ConvertNullInt(strUserID);
                        model.tran_grp_seq = null;
                        model.record_status = "A";
                        model.created_by = cn.ConvertNullInt(strUserID);
                        model.created_when = dtNow;
                        model.cheque_seq = Convert.ToInt32(strChequeSeq);

                        transfer.Insert(model);
                    }
                }
            }
        }
        public void Delete(M_FIM_Cheques info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@cheque_seq", info.cheque_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_cheques"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE cheque_seq = @cheque_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int DoPayCheckMulti(int[] paySeqs, DateTime realPayDate, DateTime payDate, int chequeStatus, int empSeq, string remark, out int countChequeEffected)
        {
            countChequeEffected = 0;
            int countPayEffected = 0;
            if (paySeqs == null || paySeqs.Length == 0) return countPayEffected;

            var fimMoneyRequestDal = new D_FIM_MoneyRequests();
            var fimPaymentDal = new D_FIM_Payments();
            List<M_FIM_Payments> pms = paySeqs.Select(fimPaymentDal.GetInfo).ToList();
            List<M_FIM_MoneyRequests> mrs = pms
                .Select(pm => pm.money_req_seq != null ? fimMoneyRequestDal.GetInfo(pm.money_req_seq.Value) : null)
                .Where(o => o != null).ToList();

            string strSQL;
            try
            {
                dbManager.Open();

                foreach (var pm in pms)
                {
                    countPayEffected++;
                    countChequeEffected++;
                    dbManager.CreateParameters(9);
                    dbManager.AddParameters(0, "@real_pay_date", realPayDate);
                    dbManager.AddParameters(1, "@updated_by", empSeq);
                    dbManager.AddParameters(2, "@pay_seq", pm.pay_seq);
                    dbManager.AddParameters(3, "@pay_date", payDate);
                    dbManager.AddParameters(4, "@receiver", (from mr in mrs where mr.money_req_seq == pm.money_req_seq select mr.payee_name).FirstOrDefault());
                    dbManager.AddParameters(5, "@remarks", remark);
                    dbManager.AddParameters(6, "@cheque_status", chequeStatus);
                    dbManager.AddParameters(7, "@actual_pay_date", realPayDate);
                    dbManager.AddParameters(8, "@cheque_date", realPayDate);

                    strSQL = @"
update fim_payments set real_pay_date = @real_pay_date, 
	updated_by = @updated_by, 
	updated_when = getdate() 
where pay_seq = @pay_seq;

update fim_cheques set pay_date	= @pay_date,
actual_pay_date	= @actual_pay_date,
cheque_date	= @cheque_date,
	receiver = @receiver,
	remarks = @remarks,
	cheque_status = @cheque_status,
	updated_by = @updated_by,
	updated_when = getdate()
where cheque_seq in (select pq.cheque_seq from fim_payments p 
inner join fim_payment_cheques pq on (p.pay_seq = pq.pay_seq and pq.record_status not like 'D')
inner join fim_cheques cheques on (pq.cheque_seq = cheques.cheque_seq)
where p.pay_seq = @pay_seq and cheques.cheque_status != 2
group by pq.cheque_seq)";

                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }

            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return countPayEffected;
        }

        private void GetSourceAndBgmYearFromCheckSeq(int checkSeq, out int? bgmSourceSeq, out int? bgmYear)
        {
            // 1. cheque->cheque book->bank account = source seq
            // 2. เอา paycheque[0].payment รู้ bgmyear, source seq
            bgmYear = null;
            bgmSourceSeq = null;
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@cheque_seq", checkSeq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = @"
select p.bgm_year, p.bgm_source_seq 
from fim_payment_cheques pq 
   inner join fim_payments p on pq.pay_seq = p.pay_seq
where pq.cheque_seq = @cheque_seq 
   and p.record_status not like @delete_status 
   and pq.record_status not like @delete_status";

                var dataTable = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dataTable.Rows.Count > 0)
                {
                    DataRow dr = dataTable.Rows[0];
                    bgmYear = cn.ConvertNullInt(dr["bgm_year"]);
                    bgmSourceSeq = cn.ConvertNullInt(dr["bgm_source_seq"]);
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            } 


        }

    }
}
